#ifndef SQLITE_TABLES_HPP
#define SQLITE_TABLES_HPP

#include "general.h"
#include "cJSON.h"
#include "Function.hpp"

using namespace std;

class Sqlite_tables{

public:
	//打开数据库，没有则创建
	 void Sqlite3_open(){
		ret = sqlite3_open("sql.db", &db);
		if(ret != SQLITE_OK){
			cout << "sqlite3_open err" << sqlite3_errmsg(db) << endl;
			
			return;
		}

		char *account_table = "create table if not exists Account_table(Account char primary key, Password char, Question char, Answer char)";
		ret = sqlite3_exec(db, account_table, NULL, NULL, &errmsg);
		if(ret != SQLITE_OK){
			cout << "sqlite3_exec create table err:" << sqlite3_errmsg(db) << endl;
			sqlite3_free(errmsg);
			
			return;
		}

		char *machine_table = "create table if not exists Machine_table(MachineId char primary key, Password char, Master char)";
		ret = sqlite3_exec(db, machine_table, NULL, NULL, &errmsg);
		if(ret != SQLITE_OK){
			cout << "sqlite3_exec create table err:" << sqlite3_errmsg(db) << endl;
			sqlite3_free(errmsg);

			return;
		}
			
		char *commodity_table = "create table if not exists Commodity_table(MachineId char, GoodsId char primary key, Name char, Price double, Stock int, Image char, Type char)";
		ret = sqlite3_exec(db, machine_table, NULL, NULL, &errmsg);
		if(ret != SQLITE_OK){
			cout << "sqlite3_exec create table err:" << sqlite3_errmsg(db) << endl;
			sqlite3_free(errmsg);

			return;
		}
			
	}

	//登录，将帐号密码与数据库比对
	char *Account_login(int comm_fd, cJSON *arrayData){
		Sqlite3_open();
		bzero(buff, sizeof(buff));

		acc = cJSON_GetObjectItem(arrayData,"Account");
		pas = cJSON_GetObjectItem(arrayData,"Password");
		
		sprintf(buff, "select * from Account_table where account = '%s'", acc->valuestring);
		sqlite3_get_table(db, buff, &result, &row, &column, &errmsg);
		if(row == 0){
			cout << "登录失败，用户名不存在" << endl;
			Func_pack(comm_fd, USER_NAME_ERR);
			
			return 0;
		}
		bzero(buff, sizeof(buff));

		sprintf(buff, "select * from Account_table where account = '%s' and Password = '%s'", acc->valuestring, pas->valuestring);
		sqlite3_get_table(db, buff, &result, &row, &column, &errmsg);
		if(row == 0){
			cout << "登录失败，密码错误" << endl;
			Func_pack(comm_fd, PASSWORD_ERR);
		}
		else{
			cout << "登录成功" << endl;
			//static string user = acc->valuestring;
			Func_pack(comm_fd, LOG_IN_SUCCESS);
		}
		bzero(buff, sizeof(buff));

		return acc->valuestring;
	}	

	//注册账户，将信息写入帐号密码表
	void Account_registration(int comm_fd, cJSON *arrayData){
		Sqlite3_open();

		acc = cJSON_GetObjectItem(arrayData,"Account");
		pas = cJSON_GetObjectItem(arrayData,"Password");
		que = cJSON_GetObjectItem(arrayData,"Question");
		ans = cJSON_GetObjectItem(arrayData,"Answer");
		
		sprintf(buff, "select * from Account_table where account = '%s'", acc->valuestring);
		sqlite3_get_table(db, buff, &result, &row, &column, &errmsg);
		if(row > 0){   //用户名已存在
			cout << "注册失败，用户名已存在" << endl;
			Func_pack(comm_fd, REGISTER_FAIL);
			
			return;
		}
		bzero(buff, sizeof(buff));

		sprintf(buff, "insert into Account_table values('%s', '%s', '%s', '%s')", acc->valuestring, pas->valuestring, que->valuestring, ans->valuestring);
		ret = sqlite3_exec(db, buff, NULL, NULL, &errmsg);
		if(ret != SQLITE_OK){
			cout << "sqlite3_exec insert err:" << sqlite3_errmsg(db) << endl;
			sqlite3_free(errmsg);
			
			return;
		}
		bzero(buff, sizeof(buff));

		cout << "注册成功" << endl;
		Func_pack(comm_fd, REGISTER_SUCCESS);
		
		return;
	}

	//注册柜机
	void Band_machine(int comm_fd, cJSON *arrayData, string user){
		Sqlite3_open();

		machine_id = cJSON_GetObjectItem(arrayData,"MachineId");
		machine_pas = cJSON_GetObjectItem(arrayData,"Password");

		const char *User = user.c_str();

		sprintf(buff, "select * from Machine_table where MachineId = '%s'", machine_id->valuestring);
		sqlite3_get_table(db, buff, &result, &row, &column, &errmsg);
		if(row > 0){   //柜机号已存在
			cout << "注册失败，柜机号已存在" << endl;
			Func_pack(comm_fd, BIND_MACHINE_ERR);
			
			return;
		}
		bzero(buff, sizeof(buff));

		sprintf(buff, "insert into Machine_table values('%s', '%s', '%s')", machine_id->valuestring, machine_pas->valuestring, User);
		ret = sqlite3_exec(db, buff, NULL, NULL, &errmsg);
		if(ret != SQLITE_OK){
			cout << "sqlite3_exec insert err:" << sqlite3_errmsg(db) << endl;
			sqlite3_free(errmsg);
			
			return;
		}
		bzero(buff, sizeof(buff));

		cout << "注册柜机成功" << endl;
		Func_pack(comm_fd, BIND_MACHINE_SUCCESS);
		
		return;
	}

	//修改柜机密码

	//移除柜机
	void Remove_machine(int comm_fd, cJSON *arrayData){
		Sqlite3_open();

		machine_id = cJSON_GetObjectItem(arrayData,"MachineId");

		cout << machine_id->valuestring << endl;
		sprintf(buff, "delete from Machine_table where MachineId = '%s'", machine_id->valuestring);
		ret = sqlite3_exec(db, buff, NULL, NULL, &errmsg);
		if(ret != SQLITE_OK){
			cout << "sqlite3_exec insert err:" << sqlite3_errmsg(db) << endl;
			sqlite3_free(errmsg);
			
			return;
		}
		bzero(buff, sizeof(buff));

		cout << "移除柜机成功" << endl;
		Func_pack(comm_fd, REMOVE_MACHINE_SUCCESS);
		
		return;
	}

	//选择柜机
	void Choise_machine(int comm_fd, string user){
		Sqlite3_open();

		cJSON *array = cJSON_CreateArray();
		const char *User = user.c_str();

		sprintf(buff, "select * from Machine_table where Master = '%s'", User);
		sqlite3_get_table(db, buff, &result, &row, &column, &errmsg);
		if(row == 0){   //没有找到柜机
			cout << "还没有注册柜机" << endl;
			Func_pack(comm_fd, CHOOSE_MAC_FAIL);
			
			return;
		}
		bzero(buff, sizeof(buff));

		int index = column;
		for(int i = 1; i < (row + 1) * column; ++i){
			if(i % column == 0){
				cout << result[i] << endl;
				cJSON *json_ret2 = cJSON_CreateObject();
				cJSON_AddItemToObject(json_ret2, "MachineId", cJSON_CreateString(result[i]));
				cJSON_AddItemToArray(array, json_ret2);
			}
		}
		MachineId_pack(comm_fd, CHOOSE_MAC_SUCCESS, array);

		return;
	}

	//增加商品信息
	
	//删除商品信息
	
	//修改商品信息
	
	//查看商品信息

	//仅有功能号的json包封装
	void Func_pack(int comm_fd, int func){
		cJSON_AddItemToObject(json_ret, "Turban", cJSON_CreateString("0xbb"));
		cJSON_AddItemToObject(json_ret, "Function", cJSON_CreateNumber(func));
		json_ptr = cJSON_Print(json_ret);
		cout << json_ptr << endl;
		write(comm_fd, json_ptr, strlen(json_ptr));	
	}

	//账户密码表封装
	
	//返回柜机号的json包封装
	void MachineId_pack(int comm_fd, int func, cJSON *array){
		cJSON_AddItemToObject(json_ret, "Turban", cJSON_CreateString("0xbb"));
		cJSON_AddItemToObject(json_ret, "Function", cJSON_CreateNumber(func));
		cJSON_AddItemToObject(json_ret, "DataArray", array);
		json_ptr = cJSON_Print(json_ret);
		cout << json_ptr << endl;
		write(comm_fd, json_ptr, strlen(json_ptr));	
		
		return;
	}
	
	//商品信息表封装
	void CommodityData_pack(int comm_fd){
		
	//cJSON_CreateS tringArray(const char **strings, int count);
	
	}	

private:
	sqlite3 *db = NULL;
	char buff[MAXLINE] = {0};
	char **result = NULL;
	int row = -1;
	int column = -1;
	int ret;
	char *errmsg;
	char *json_ptr = NULL;
	//char *user;
	cJSON *json_ret = cJSON_CreateObject();  //第一层键值
	//cJSON *json_ret2 = cJSON_CreateObject();  //第一层键值
	cJSON *acc;           //帐号
	cJSON *pas;           //密码
	cJSON *que;           //密保问题
	cJSON *ans;           //密保答案
	cJSON *machine_id;   //柜机号
	cJSON *machine_pas;   //柜机密码
};


#endif
